package com.scenery.server.services;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.json.JSONException;
import org.json.JSONObject;

import com.scenery.server.vo.Scenery;

public class SceneryServices extends Manager
{
	public static List<Scenery> list(String cityid)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select  `sceneryId`, `sceneryName`,  `grade`,`address`,`cityId`,`provinceId`,`intro`,`buyNotice`,`payMode`,`lon`,`lat` from  lv_scenery where cityId="+cityid;
		int ret=1;
		String pwd="";
		Calendar calendar = Calendar.getInstance();
		Date date = calendar.getTime();
		List<Scenery> list=new ArrayList<Scenery>();
		SimpleDateFormat ymdhmsf = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		String reqTime = ymdhmsf.format(date);
		conn = getConnection();
		if (conn != null) {
			try {
				ps = conn.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Scenery bean =new Scenery();
					bean.setSceneryId(rs.getString(1));
					bean.setSceneryName(rs.getString(2));
					bean.setGrade(rs.getString(3));
					bean.setAddress(rs.getString(4));
					bean.setCityId(rs.getString(5));
					bean.setProvinceId(rs.getString(6));
					bean.setIntro(rs.getString(7));
					bean.setBuyNotice(rs.getString(8));
					bean.setPayMode(rs.getString(9));
					bean.setLon(rs.getString(10));
					bean.setLat(rs.getString(11));
					
					list.add(bean);
				}
				rs.close();
				ps.close();
				conn.close();
				ret=0;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				ret=3;
			}

		}
		return list;
	}
	public int insert(Scenery scenery)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		
		String sql = "insert into  lv_scenery ( `sceneryId`, `sceneryName`,  `grade`,`address`,`cityId`,`provinceId`,`intro`,`buyNotice`,`payMode`,`lon`,`lat`) " +
				"VALUES  (?,?,?,?,? ,?,?,?,?,? ,?) ";
		int ret=1;
		String pwd="";
		Calendar calendar = Calendar.getInstance();
		Date date = calendar.getTime();

		SimpleDateFormat ymdhmsf = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		String reqTime = ymdhmsf.format(date);
		conn = getConnection();
		if (conn != null) {
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, scenery.getSceneryId());
				ps.setString(2,scenery.getSceneryName());
				ps.setString(3, scenery.getGrade());				
				ps.setString(4, scenery.getAddress());
				
				ps.setString(5, scenery.getCityId());
				ps.setString(6,scenery.getProvinceId());
				ps.setString(7, scenery.getIntro());				
				ps.setString(8, scenery.getBuyNotice());
				
				ps.setString(9, scenery.getPayMode());
				ps.setString(10,scenery.getLon());
				ps.setString(11, scenery.getLat());				
				
				
				ps.executeUpdate();
				ps.close();
				conn.close();
				ret=0;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				ret=3;
			}

		}
		return ret;
	}
	public static void main(String args[])
	{
		p(list("133").size());
		List<Scenery> list=new ArrayList<Scenery>();
		list=list("133");
		JSONObject o=new JSONObject();
		try {
			o.put("code", "haha");
			o.put("list", list);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			JSONObject o2=new JSONObject(o.toString());
			p(o2.toString());
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
}
